package com.sharemarking.system.mapper;

import com.sharemarking.system.dto.orgVerify.OrgVerifyListDto;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @author dxy
 */
@Repository
public interface ExcelImportMapper {

    @Select("SELECT DATE_FORMAT(q.create_date,'%Y年%m月%d日') AS create_date,(SELECT real_name FROM sys_org WHERE id = q.receive_org_id) AS manage_org_name,  " +
            "            q.waste_type_name,q.waste_name,(SELECT IFNULL(real_name,name) FROM sys_org WHERE id = q.transport_org_id) AS transport_org_name,q.code,q.weight*0.01 AS weight, " +
            "            (SELECT IFNULL(real_name,nick_name) FROM sys_member WHERE id = q.send_org_member_id) AS send_org_member,q.receive_weight*0.01 AS receive_weight  " +
            "            FROM ( " +
            "            SELECT a.create_date,a.receive_org_id,b.waste_type_name,b.name AS waste_name,a.transport_org_id,b.weight,a.send_org_member_id,B.real_weight AS receive_weight,a.code " +
            "            FROM wa_order a LEFT JOIN wa_order_details b ON a.id = b.order_id WHERE a.send_org_id = 10 AND a.type = 1 AND a.state = 9 )q")
    List<Map<String, Object>> getPwExcelInfo(Integer orgId);

    @Select("SELECT DATE_FORMAT(q.create_date,'%Y年%m月%d日') AS create_date, " +
            " (SELECT real_name FROM sys_org WHERE id = q.receive_org_id) AS manage_org_name, " +
            " (SELECT nick_name FROM sys_member WHERE id = q.receive_org_member_id) AS receive_org_member_name, " +
            " q.waste_type_name," +
            " (SELECT real_name FROM sys_org WHERE id = q.send_org_id) AS send_org_name, " +
            "(SELECT nick_name FROM sys_member WHERE id = q.transport_org_member_id) AS transport_org_member_name, " +
            "(SELECT real_name FROM sys_org WHERE id = q.driver_id) AS driver_name, " +
            "(SELECT number FROM wa_org_car WHERE id = q.car_id) AS car_id, " +
            "(SELECT type FROM wa_org_car WHERE id = q.car_id) AS car_type, " +
            " q.waste_name, " +
            " (SELECT IFNULL(real_name,name) FROM sys_org WHERE id = q.transport_org_id) AS transport_org_name, " +
            " q.code,q.weight*0.01 AS weight, " +
            " ( " +
            " SELECT IFNULL(real_name,nick_name) FROM sys_member  " +
            " WHERE id = q.send_org_member_id " +
            " ) AS send_org_member, " +
            " q.receive_weight*0.01 AS receive_weight " +
            " FROM ( " +
            " SELECT  " +
            " a.create_date,a.receive_org_id,b.waste_type_name,b.name AS waste_name, " +
            " a.transport_org_id,b.weight,a.send_org_member_id,B.real_weight AS receive_weight, a.send_org_id,a.transport_org_member_id,a.car_id,a.driver_id," +
            " a.code,a.receive_org_member_id " +
            " FROM wa_order a LEFT JOIN wa_order_details b  " +
            " ON a.id = b.order_id " +
            " WHERE " +
            " a.receive_org_id = #{orgId}  " +
            " AND a.type = 1 AND a.state = 9 )q")
    List<Map<String, Object>> getJyExcelInfo(Integer orgId);

    List<Map<String, Object>> getYsExcelInfo(Integer orgId);

    @Select("SELECT a.org_id id,a.name orgName " +
            "from wa_org_verify a LEFT JOIN sys_member b ON a.member_id=b.id " +
            "where exam_state = 1 " +
            "AND b.role = 0")
    List<OrgVerifyListDto> getProductWasteOrgList();

    @Select("SELECT a.org_id id,a.name orgName " +
            "from wa_org_verify a LEFT JOIN sys_member b ON a.member_id=b.id " +
            "where exam_state = 1 " +
            "AND b.role = 1")
    List<OrgVerifyListDto> getManagementOrgList();

    @Select("SELECT a.id,a.real_name orgName " +
            "from sys_org a LEFT JOIN sys_member b ON a.member_id=b.id " +
            "where a.type=2")
    List<OrgVerifyListDto> getTransportOrgList();

}
